#include <sqlite3.h>
#include "../include/staff_db.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

struct sqlite3 *staff_db = NULL;

#define FILEPATH_MAX 256  //文件路径的最大长度
#define MAX_SQL_SEL 128 //SQL语句的最长长度


const char *init_stafftable[7] = { "create table staff_table (ID char(6) primary key not null, name text not null, password text not null,  addr text ,  age int , salary float , flag tinyint not null )",
"insert into staff_table values ('000001','xiaozhang','123456','taian',18,4000.0 ,0)", 
"insert into staff_table values ('000002','xiaowang','123asd','heze',19,4500.0 ,0)",
"insert into staff_table values ('000003','xiaoming','love.@','jinan',20,3500.0 ,1)",
"insert into staff_table values ('000004','xiaohong','zxcjkl','guangdong',21,5600.0 ,0)",
"insert into staff_table values ('000005','xiaozhao','123zxc','beijing',19,3999.0, 0)","0" };

int Init_db(const char *dbName)//初始化，并且打开数据库文件,dbname为数据库文件的名字("staff.db")
{

    int iRet = 0;
    char *pPath = NULL; //存放当前文件的路径
    pPath = (char *) malloc (FILEPATH_MAX + 10);
    if (NULL == pPath)
    {
        perror("Init_db error,fail to malloc");
        return -1;
    }
    memset(pPath, 0, FILEPATH_MAX + 10);
    getcwd(pPath, FILEPATH_MAX);//获得当前文件的路径
    puts("---------------");
    printf("%s %ld\n",pPath,strlen(pPath));
    memcpy(pPath + strlen(pPath), dbName, strlen(dbName));
    printf("%s %ld\n",pPath,strlen(pPath));
    /*db name end*/
    //init db
    staff_db = NULL;
    iRet = sqlite3_close(staff_db);
    if(iRet != SQLITE_OK)
    {
        printf("Init_db error,fail to close staff_db:%s\n",sqlite3_errmsg(staff_db)); //打印错误信息
        return -1;
    }
     //open or create db
    iRet = sqlite3_open((const char *)pPath,&staff_db);
    if(iRet != SQLITE_OK)
    {
        printf("Init_db error,fail to open staff_db:%s\n",sqlite3_errmsg(staff_db)); //打印错误信息
        return -1;
    }
    
    free(pPath);
    pPath = NULL;
    return 0;
}

int exec_sql(const char **sql_set) //执行sql语句,sql_set 是一个字符串数组,里面是sql语句集合
{//create table
    if (NULL == sql_set || NULL == staff_db)
    {
        puts("sql_set error");
        return -1;
    }
    char *pErrMsg = NULL;
    //create and insert
    int i = 0;
    while(strcmp(sql_set[i],"0"))
    {
        printf("sql_set is %s\n",sql_set[i]);
        puts("____________________________");
        if (SQLITE_OK !=sqlite3_exec(staff_db,sql_set[i],0,0,&pErrMsg))//向表中插入５行数据
        {
            printf("exec_sql error:%s\n",pErrMsg);
            i ++;
            continue;
        }
        i ++;
        printf(" i = %d \n",i);
    }
    return 0;
}
//show table
int show_table(const char *table_name) //显示自己定义好的数据库里面的表格,table_name是表格的名字
{
    char *sqlSelect = (char *)malloc(sizeof(char) * MAX_SQL_SEL);
    strcpy(sqlSelect ,"select * from ");
    strcat(sqlSelect ,table_name);
    char** ppTable = NULL;
    char* pErrMsg = NULL;
    int iRow = 0;
    int iColumn = 0;

    int iResult = sqlite3_get_table(staff_db ,sqlSelect ,&ppTable ,&iRow ,&iColumn ,&pErrMsg);
    if(iResult != SQLITE_OK)
    {
        printf("fail to show table, get table failed:%s\n",pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    sqlite3_free(pErrMsg);
    printf("select sql row %d column %d\n", iRow, iColumn);
    if (NULL != ppTable)
    {
        puts("-------------------------------------------------");
        int i = 0;
        for (; i <= iRow; i++)
        {
            int j = 0;
            for (; j < iColumn; j++)
            {
                if (NULL != ppTable[i * iColumn + j])
                {
                    if(j == 0)
                    {
                        printf("%-20s",ppTable[i * iColumn + j]);
                    }
                    else
                        printf("%-24s",ppTable[i * iColumn + j]);
                }
            }
            printf("\n");
        }
        puts("-------------------------------------------------");
    }
    if (NULL != ppTable)
    {
        sqlite3_free_table(ppTable);//将结果释放掉
    }
    free(sqlSelect);
}
int close_db(void) //关闭数据库
{
    if (NULL == staff_db)
    {
        return 0;
    }
    int iResult = sqlite3_close(staff_db);
    
    if(SQLITE_OK != iResult)
    {
        puts("fail to close staff_db");
        return iResult;
    }
    staff_db = NULL;
    return 0;
}


/* 适用范围,用户登录时使用 */
/*功能:核对员工名字和密码是否正确 */
/*参数:要核对的员工的名字 name ; 密码 password
 *返回值 查询失败返回-1, 密码正确返回1,错误返回0
 *作者:丁国亮 */

int do_staff_check_info(char *name, char *password) //核对用户名字和密码,密码正确返回1,错误返回0,查询失败返回-1
{
    char sql_selct[MAX_SQL_SEL] = "";

    sprintf(sql_selct, "select *from staff_table where name = '%s' and password = '%s'", name, password);
    char** ppTable = NULL;
    char* pErrMsg = NULL;
    int iRow = 0, iColumn = 0;

    int iResult = sqlite3_get_table(staff_db, sql_selct, &ppTable, &iRow, &iColumn, &pErrMsg);

    if(iResult != SQLITE_OK)
    {
        printf("fail to check staff info,get table faild %s \n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    sqlite3_free(pErrMsg);
    if(ppTable != NULL)

    sqlite3_free_table(ppTable);//将结果释放掉

    if(iRow == 0) //表示没有找到
    {
        puts("The password or name wrong!");
        return 0;
    }
    else
    {
        puts("The password is right");
        return 1;
    }

}


/*适用范围:用户登录后使用
 *函数功能:确定用户是普通用户还是管理员
 *参数:用户姓名
 *返回值: -1表示查找失败,0表示普通用户,1表示管理员
 *作者:丁国亮
 */

int do_staff_check_identify(char *name) //返回-1,表示查找失败或名字不存在,返回0 表示普通用户,返回1 表示管理员
{
    char sql_selct[MAX_SQL_SEL] = "";

    sprintf(sql_selct, "select flag from staff_table where name = '%s'", name);
    char** ppTable = NULL;
    char* pErrMsg = NULL;
    int iRow = 0, iColumn = 0;

    int iResult = sqlite3_get_table(staff_db, sql_selct, &ppTable, &iRow, &iColumn, &pErrMsg);

    if(iResult != SQLITE_OK)
    {
        printf("fail to check staff info,get table faild %s \n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    sqlite3_free(pErrMsg);

    if(iRow == 0) //表示没有找到
    {
        printf("Not find name :%s\n", name);
        if(ppTable != NULL)
        {
            sqlite3_free_table(ppTable);//将结果释放掉
        }
        return -1;
    }
    else
    {
        if(!strcmp(ppTable[1], "1"))
        {
            sqlite3_free_table(ppTable);//将结果释放掉
            puts("超级用户");
            return 1;

        }
        else
        {
            sqlite3_free_table(ppTable);//将结果释放掉
            puts("普通用户");
            return 0;
        }
    }

}

//适用范围:管理员
//功能:增加用户信息
//参数:用户信息结构体变量
//返回值 0表示成功,-1表示失败
//作者:李超

int do_staff_add_user(INFO x)
{
    char *errmsg = NULL;
    char cmd[256] = "";
    sprintf(cmd,"insert into staff_table values ('%s' , '%s','%s','%s',%d,%f,%d)",x.id , x.name, x.password, x.addr, x.age, x.salary, x.flag);
    printf("cmd = %s\n",cmd);

    if(sqlite3_exec(staff_db,cmd,NULL,NULL,&errmsg)!=SQLITE_OK)
    {
        printf("error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    else
    {
        sqlite3_free(errmsg);
        puts("insert ok");
        return 0;
    }

}

//适用范围:管理员删除用户时使用
//功能:判断用户名是否存在
//参数:name 查找哪个用户名
//返回值 1 表示用户存在 0 表示用户不存在 -1 表示查找失败
//作者:盘水养


int is_name_exist(char *name)	 
{	
	char sqlSelect[256] = "";


	sprintf(sqlSelect,"select *from staff_table where name = '%s'",name); 
	char** ppTable = NULL;
	char* pErrMsg = NULL;
	int iRow = 0;
	int iColumn = 0;

	int iResult = sqlite3_get_table(staff_db ,sqlSelect ,&ppTable ,&iRow ,&iColumn ,&pErrMsg);

	if(iResult != SQLITE_OK)
	{
		printf("fail to get:%s\n",pErrMsg);

		sqlite3_free(pErrMsg);
		return -1;
	}
	sqlite3_free(pErrMsg);

	if (0!= iRow)//找到名字是否在表中的某行
	{
		sqlite3_free_table(ppTable);//没有找到员工就释放ppTable
		return 1;

	}
	else
	{
		sqlite3_free_table(ppTable);//没有找到员工就释放ppTable
		return 0;
	}
}


// 使用范围:管理员
// 功能:删除一条用户信息
// 入口参数 :name
// 返回值:删除成功返回 0 ,失败返回-1
// 作者:宋亚武 

int do_staff_del_user(char *name)
{
    char *errmsg = NULL;
    char cmd[128] = "";

    /*
     * 在这里插入判断用户名是否存在的代码
     */

    if(!is_name_exist(name))
    {
        printf("Fail to del user name %s ,the name is not exist.\n", name);
        return -1;
    }
    sprintf(cmd, "delete from staff_table where name = '%s' ", name);
    printf("%s\n",cmd);

    if(sqlite3_exec(staff_db, cmd, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        sqlite3_free(errmsg);
        printf("error!\n%s\n",errmsg);
        return -1;
    }
    else
    {
        sqlite3_free(errmsg);
        puts("delete is ok!");
    }
    return 0;
}

 //参数：要查询的名字
 //功能：按照名字来查询员工信息
 //适用：管理员用户和普通用户通用
 //返回值：NULL则表示失败，非NULL则成功 
 //作者:盘水养

char **do_search_userinfo(char *name) 
{	
	char sqlSelect[256] = "";
	
	
	sprintf(sqlSelect,"select *from staff_table where name = '%s'",name); 
	char** ppTable = NULL;
	char* pErrMsg = NULL;
	int iRow = 0;
	int iColumn = 0;
	
	int iResult = sqlite3_get_table(staff_db ,sqlSelect ,&ppTable ,&iRow ,&iColumn ,&pErrMsg);
	
	if(iResult != SQLITE_OK)
	{
		printf("fail to show table, get table failed:%s\n",pErrMsg);
		
		sqlite3_free(pErrMsg);
		return NULL;
	}
	sqlite3_free(pErrMsg);

	int i = 0;
	int j = 0;
	if (0!= iRow)//找到名字是否在表中的某行
	{
		printf("已找该员工\n");

		for(j = 0; j <= iRow; j++)//用2个for循环来打印出表的第一行信息和找到员工的那一行数据
		{
			for(i = 0;i < iColumn;i++ )
			{
				printf("%-10s ", ppTable[i + j * iColumn]);
			}
			printf("\n"); 
		}
			return ppTable;
	}
	else
	{
	    printf("对不起,没有该员工\n");
		sqlite3_free_table(ppTable);//没有找到员工就释放ppTable
		return NULL;
	}
	
}
 //适用：适用于普通用户修改密码
 //功能：修改员工密码
 //参数：要查询的名字
 //返回值：0 表示成功, -1表示失败
 //作者:杨宁
int do_staff_change_passwd(char *name,char *newpassword)
{
	char cmd[254] = "";
	char *errmsg = NULL;
	sprintf(cmd,"update staff_table set password = '%s' where name = '%s'",newpassword,name);
	printf("password_cmd = %s\n",cmd);

	if(sqlite3_exec(staff_db,cmd,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("password change error: %s\n",errmsg);
		return -1;
	
	}else{
		puts("password change ok");
		return 0;
	}
}

//适用：适用于管理员修改用户信息
 //功能：修改员工信息
 //参数：
 //返回值：0 表示成功, -1表示失败
 //作者:杨宁

int do_staff_change_userinfo(INFO x)
{
    if(!is_name_exist(x.name))
    {
		printf("name change fail :%s not exist\n",x.name);
        return 1;

    }
	char *errmsg = NULL;
	char cmd[256] = "";
	sprintf(cmd,"update staff_table set id = '%s', addr = '%s', age  = %d, salary  = %0.1f, flag = %d where name = '%s'",x.id,x.addr,x.age,x.salary,x.flag,x.name);

	if(sqlite3_exec(staff_db,cmd,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("info change error: %s\n",errmsg);
		return -1;
	}else{
		printf("info change success\n");
		return 0;
	}

}





